

clear
set more 1
set type double

*this uses 2020 currency data but only up to 2019 for inputs because they are not all available for the full year
*manuf indicator only uses up to 2017 accounts data

*****************************************************************
*VARIABLES
*A. firm level variables
*1. tfp index
*2. export shares
*3. RD
*4. financial variables

*B. country level and industry level
* country i exports to the world
* HS4 exports to the world

*******************************************************************************************

********************************************************************************
*1. tfp index
***** TFP computation using the Tornqvist Index Approach  (like Solow residual) **
**********************************************************************************
*(see details paper of Van Biesebroeck, Journal of Industrial Economics)

use "../../rawdata/annual accounts update.dta", clear	

keep if year>=2012 
drop if missing(nacerev2)
gen manuf_firm=nacerev2>=10 & nacerev2<=33 	
keep if manuf_firm==1

gen wagebill=C_62
gen addedvalue=C_9800
gen emp_FTE=C_1003
gen tfa=C_22_27


gen ls = wagebill / addedvalue
	replace ls=1 if ls>1 & ls!=.
	replace ls=0 if ls<0 & ls!=.

	xtset vat year
	gen lag_ls = L.ls


* Tornqvist Index
egen mean_ls = mean(ls), by(nacerev2)
gen tilde_ls = (ls + mean_ls)/2
gen tilde_cs = (1 - tilde_ls)
egen mean_va = mean(addedvalue), by(nacerev2)
egen mean_l = mean(emp_FTE), by(nacerev2)
egen mean_k = mean(tfa), by(nacerev2)
gen tfp_n = (addedvalue/mean_va)/((emp_FTE/mean_l)^tilde_ls * (tfa/mean_k)^tilde_cs)
gen lntfp_n = ln(tfp_n)
gen dlntfp_n = lntfp_n - L.lntfp_n

keep vat year lntfp_n tfp_n dlntfp_n
label var lntfp_n "log normalized TFP index, value added"

drop if missing(lntfp_n)
keep if year==2017

save temp/tfpindex,replace	

********************************************
*2. export shares - average 2017 to 2019
********************************************
*i. construct extra-eurozone exports by firm
*********************************************
use ../../rawdata/X_2019, clear 
	append using ../../rawdata/X_2018 
	append using ../../rawdata/X_2017

*clean data
	drop if land=="EU"
	drop if vat>999999000 & vat!=.
	
collapse (sum) value, by(vat land month year flow)
	gen eurozone = inlist(land, "AT", "CY", "EE", "FI", "FR", "DE", "GR", "IE", "IT") 
	replace eurozone =1 if inlist(land, "LV", "LT", "LU", "MT", "NL", "PT", "SK", "SI", "ES")
	replace eurozone = 1 if inlist(land, "AD", "MC", "SM", "XK", "ME") 
	rename land cty_code
	
	gen value_eu=value*eurozone
	gen value_xeu=value*(1-eurozone)
	
merge m:1 cty_code using ../data/usd_peg, keep(match master) nogen
	gen value_peg=value*(usd_peg==1)
	collapse (sum) value*, by(vat year)
save temp/xvalue_fc_2017to2019, replace


*ii. collapse turnover data
*********************************

use ../../rawdata/vat_declaration_monthly_updated, clear
	keep if year==2017|year==2018
	collapse (sum) turnover, by(vat year)
save temp/turnover_2017to2018, replace

*add in	2019 
use ../../rawdata/update_feb21/monthly&quarterly_salesVAT_2019_2020.dta, clear
	keep if year==2019
	collapse (sum) turnover, by(vat year)
	append using temp/turnover_2017to2018
save temp/turnover_2017to2019, replace

*iii. construct ez export share variables for export currency data
* 8% of exporters have no turnover data
***********************************************************************
	
use temp/xvalue_fc_2017to2019, clear	
	replace value_eu=0 if missing(value_eu)
	gen ez_exportsh = value_eu/value
	
	replace ez_exportsh=1 if ez_exportsh>1 & !missing(ez_exportsh)
	collapse (mean) ez_exportsh, by(vat)
		
	label var ez_exportsh "ez exports as share of total exports averaged 2017to2019"
save temp/xshare_vat_2017to2019, replace

*iv. construct exports as share of sales
********************************************************
use temp/xvalue_fc_2017to2019, clear
	merge 1:1 vat year using temp/turnover_2017to2019, keep(match)
	drop if missing(turnover)
	drop if turnover<=0
	gen exports_sales=value/turnover
	collapse (mean) exports_sales, by(vat)
save temp/exports_sales, replace


****************************************************
*3. RD share
******************************************************
use "../../rawdata/panel_expim_rd10_to_rd18.dta" ,clear
	rename btw vat
	keep vat expimkEur17 turnkEur17
	rename expimkEur17 RD
	gen shRD=RD/turnkEur17
	
	label var RD "intra euros RD expenditures in 1000 euro in 2017"
	label var shRD "share of RD in turnover in 2017"

save temp/RD, replace


**************************************************************
*4. financial variables
*************************************************************
use "../../rawdata/annual accounts update.dta", clear
	keep if year==2017
	
	rename turnover sales
	rename C_17 LT_debt
	rename C_42_48 ST_debt
	rename C_20_58 tot_assets
	rename C_65 fin_charges
	rename C_9800 operating_profit
	rename C_9087 employment
	rename C_62 wage
	rename C_9901 profit
	rename C_54_58 cash
	
	
*create financial variables
	gen cash_STdebt=cash/ST_debt
	gen leverage=(ST_debt+LT_debt)/tot_assets
	gen coverage=operating_profit/fin_charges
			
	label var cash_STdebt " cash divided by current debt"
	label var leverage "total debt to total assets"
	label var coverage "operating profit relative to financial charges"
		
	keep vat  cash_STdebt leverage coverage tot_assets
		
	
save temp/finance_vars, replace	


**************************************************
*append all firm-level variables
****************************************************
clear
	use temp/finance_vars
		merge 1:1 vat using temp/tfpindex, nogen
		merge 1:1 vat using temp/xshare_vat_2017to2019, nogen
		merge 1:1 vat using temp/exports_sales, nogen
		merge 1:1 vat using temp/RD, nogen
	save ../data/Additional_Variables, replace



***********************************************************
*B. More aggregate variables - at country level, and industry level
*******************************************************************
************************************************************************

clear
forv y= 2017/2019 {
	append using ../../rawdata/comtrade_trade/`y'_H2012_exports.dta
	}
		
	rename *_code *_num
	rename *_iso *_iso3
	
	gcollapse (sum) val, by(i_* j_* hs6 year)
	
	* Concord iso3 codes to iso2 codes
	rename i_iso3 iso3
	merge m:1 iso3 using ../rawdata/countrycodeconcord.dta, keep(1 3) nogen keepusing(iso2)
	rename iso2 i_iso2
	rename (iso3 j_iso3) (i_iso3 iso3)
	merge m:1 iso3 using ../rawdata/countrycodeconcord.dta, keep(1 3) nogen keepusing(iso2)
	rename iso2 j_iso2
	rename iso3 j_iso3

*drop if Belgium is importer or exporter
*drop "other" nes etc
******************************************
	drop if i_iso3=="BEL"| j_iso3=="BEL"
	drop if i_iso3=="WLD"|j_iso3=="WLD"
	drop if missing(i_iso3)|missing(j_iso3)
	destring year, replace
	destring i_num, replace
	rename i_iso2 cty_code

*collapse by country
preserve
	gcollapse (sum) exports_c = val, by(year cty_code)
	collapse (mean) exports_c , by(cty_code)
	gen lnexports_c=ln(exports_c)
save ../data/exports_country, replace	
restore

*collapse by hs4
	gen hs4 = substr(hs6,1,4)
	gcollapse (sum) exports_hs4 = val, by(hs4 year)
	collapse (mean) exports_hs4, by(hs4)
	gen lnexports_hs4=ln(exports_hs4)
	destring hs4, replace
save ../data/exports_hs4, replace	


************************************************	
*erase temp files
	erase temp/tfpindex.dta
	erase temp/xshare_vat_2017to2019.dta
	erase temp/exports_sales.dta
	erase temp/RD.dta
	erase temp/xvalue_fc_2017to2019.dta
	erase temp/turnover_2017to2018.dta
	erase temp/turnover_2017to2019.dta
	
	
	
	